Validating the data warehouse

Data warehouse validation should be performed each time the model is edited (after the data warehouse has already been created). Validating the data warehouse allows you automatically resolve any differences between the model and the data warehouse.

For a data warehouse to be considered valid, the tables defined in the data warehouse need to be identical to the physical tables in terms of metadata. Depending on the change, this may require adjusting the physical tables or dropping and recreating them (via Compose).

Sometimes, however, the differences between the model and the data warehouse cannot be resolved automatically. In such cases, you need to drop and recreate the tables as described in Dropping and recreating tables.

To validate the data warehouse:

1. Click the Validate button at the bottom right of the Data Warehouse panel. The Validating the Data Warehouse progress window opens.

   If any differences are detected, the following message will be displayed: The data warehouse is different from the model.

2. Click Close. The Model and Data Warehouse Comparison Report window opens.

3. Review the report and then click Adjust Automatically to resolve the differences automatically or Generate Adjust Script to generate a script with the adjust commands.

   Information note

   • The Adjust Automatically button will be disabled either if the Generate DDL scripts but do not run them option is selected or if Compose is unable to automatically adjust the data warehouse. In such cases, you should click Generate Adjust Script as described below.
   • Due to Google Cloud BigQuery limitations, if Compose is unable to automatically adjust the data warehouse, then the generated script may be not valid either. Consequently, users should review the script carefully and adjust it manually (if required) before running.

   • If you clicked Adjust Automatically, the Adjust Data Warehouse progress window opens.

     When the "The data warehouse was adjusted successfully." message is displayed, you can close the window. Note that adjusting the data warehouse may require you to update the data mart. In such a case, an appropriate message will be displayed for each of the data marts that require updating.

     Information note

     Cases where Compose is unable to automatically adjust the data warehouse are as follows:

     • A data type change that is not supported by the database or a data type change that may result in data loss.
     • A change in an entity’s business key or distribution key.
     • An attribute’s history type is Type 2 and the satellite table number in the attribute’s settings has changed.

   • If you clicked Generate Adjust Script, the Generate DDL Scripts window opens showing the progress of the script generation.

     The generated scripts will be saved to:

     <product_dir>\data\projects\<project_name>\ddl-scripts

     Once the script(s) have been generated, you can close the Generate DDL Scripts window.

     After you close the Generate DDL Scripts window, the DDL Script Files window opens automatically displaying the generated scripts. The DDL Script Files provides a read-only view that allows you to review the scripts and download them.

     The scripts need to be executed directly in your data warehouse. Make sure that any modifications that you make to the scripts are done prior to executing them.

     Information note

     When you run the adjust scripts, backup tables are created from the existing tables. The backup table names are appended with an "_old" suffix and must be deleted manually after the script completes.

     Information note

     Search for "TODO" in the script to locate the part of the script that needs modifying.